package com.jandar.parser.impl;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;

import com.jandar.Resources;
import com.jandar.parser.BaseParser;
import com.jandar.parser.exception.ParserException;
import com.jandar.table.config.Column;
import com.jandar.table.config.ColumnTypeInfo;
import com.jandar.table.config.IdGeneratorInfo;
import com.jandar.table.config.Table;
import com.jandar.util.DBUtils;
import com.jandar.util.DateUtils;
import com.jandar.util.ExceptionUtils;
import com.jandar.util.SQLUtils;

/**
 * EXCEL解析器。
 * 
 * @author pqj
 * @version 2009-6-10
 */
public class XLSParser extends BaseParser {

	/** 是否允许使用多个sheet */
	private boolean manySheets = false;

	public XLSParser() {

		manySheets = Resources.getInstance().isManySheets();
	}

	/**
	 * 插入或者更新操作。 以最大限度处理文件中的记录，有出错的记录，写入日志。 统计插入条数、更新条数、失败条数。
	 * 
	 * @param conn 数据库连接
	 * @param file 指定文件
	 * @throws Exception 异常
	 */
	public void insertOrUpdate(Connection conn, File file) throws Exception {
		super.insertOrUpdate(conn, file);

		Workbook book = Workbook.getWorkbook(file);
		String tableName = super.getTableName(file);
		Statement statement = conn.createStatement();
		String failureFileName = Resources.getInstance().getErrfolder().getAbsolutePath() + "\\"
				+ getFailureRecordFileName(tableName, new Date());

		if (manySheets) {
			Sheet[] sheets = book.getSheets();
			for (Sheet sheet : sheets) {
				execute(statement, tableName, sheet, failureFileName);
			}
		} else {
			Sheet sheet = book.getSheets()[0];
			execute(statement, tableName, sheet, failureFileName);
		}

		try {
			DBUtils.closeStatement(statement);
		} catch (SQLException e) {
			ExceptionUtils.error(e);
		}
	}

	/**
	 * 执行一个sheet。
	 * 
	 * @param statement SQL执行对象
	 * @param tableName 表名
	 * @param sheet sheet
	 * @param failureFileName 交换错误记录文件名
	 * @throws Exception 异常
	 */
	private void execute(Statement statement, String tableName, Sheet sheet, String failureFileName) throws Exception {
		ResultSet result = null;
		String dataTitleLine = "";
		int rowCount = sheet.getRows();
		Table table = Resources.getInstance().getTableCfgMap().get(tableName);
		if (table == null) {
			throw new ParserException("找不到表：<" + tableName + ">的配置信息！");
		}

		Column[] columns = table.getAllColumn();
		IdGeneratorInfo generatorInfo = SQLUtils.getIdGenerator(table.getId());
		int idSize = table.getId().size();
		int option = 0;
		
		// 数据文件第一行是否含有标题
		int i = 0;
		boolean dataTitle = Resources.getInstance().isDataTitle();
		if (dataTitle) {
			i = 1;
			Cell[] row = sheet.getRow(0);
			StringBuilder buf = new StringBuilder();
			for (Cell c : row) {
				if (buf.length() > 0) {
					buf.append("\t");
				}
				buf.append(c.getContents());
			}
			buf.append(NEWLINE);
			dataTitleLine = buf.toString();
		}
		
		for (; i < rowCount; ++i) {
			// 强制中断标志
			if (cancelled) {
				throw new ParserException("Excel解析异常中断！");
			}
			
			Cell[] row = sheet.getRow(i);
			String[] values = null;
			
			try {
				values = getValues(row, columns, generatorInfo, idSize);
				// 查看数据库是否有数据
				String selectSQL = SQLUtils.createSelectCountSQL(tableName, values);
				logger.debug("查询记录是否存在SQL： " + selectSQL);
				result = statement.executeQuery(selectSQL);

				String operateSQL = null;
				// 数据库已经存在该记录
				if (result.next() && result.getInt(1) > 0) {
					operateSQL = SQLUtils.createUpdateSQL(tableName, values);
					option = UPDATE_OPTION;

					// 数据库没有存在该记录
				} else {
					operateSQL = SQLUtils.createInsertSQL(tableName, values);
					option = INSERT_OPTION;
				}

				logger.debug("操作记录SQL： " + operateSQL);
				statement.executeUpdate(operateSQL);
				if (INSERT_OPTION == option) {
					++insertCount;
				} else {
					++updateCount;
				}
			} catch (Exception e) {
				ExceptionUtils.error(e);
				++failureCount;
				// 写入日志文件
				writeFailureRecord(failureFileName, values, row.length);

			} finally {
				try {
					DBUtils.closeResultSet(result);
				} catch (SQLException e) {
					ExceptionUtils.error(e);
				}
			}
		}
	}

	/**
	 * 取得一行单元格所包含的字段值。
	 * 
	 * @param line 一行单元格
	 * @param column 数据库字段
	 * @param generatorInfo 主键生成策略
	 * @param idSize ID个数
	 * @return 字段值
	 */
	private String[] getValues(Cell[] cells, Column[] column, IdGeneratorInfo generatorInfo, int idSize) {

		String[] values = new String[column.length];

		for (int i = 0; i < cells.length; ++i) {

			CellType type = cells[i].getType();

			// 字符串->VARCHAR
			if (type == CellType.LABEL) {

				values[i] = cells[i].getContents();
				// 数字->NUMBER
			} else if (type == CellType.NUMBER) {

				ColumnTypeInfo colType = column[getColumnIndex(generatorInfo, idSize, i)].getType();
				if (colType == ColumnTypeInfo.STRING) {
					values[i] = cells[i].getContents();
				} if (colType == ColumnTypeInfo.NUMBER) {
					NumberCell fcell = (NumberCell) cells[i];
					values[i] = Double.toString(fcell.getValue());
				} else {
					values[i] = cells[i].getContents();
				}
				
				// DATE->DATE, DATETIME
			} else if (type == CellType.DATE) {

				DateCell fcell = (DateCell) cells[i];
				ColumnTypeInfo colType = column[getColumnIndex(generatorInfo, idSize, i)].getType();
				Date cellDate = fcell.getDate();
				cellDate.setTime(cellDate.getTime() - 8 * 60 * 60 * 1000);
				if (ColumnTypeInfo.DATE == colType) {
					values[i] = DateUtils.format(SQLUtils.DEFAULT_DATE_PATTERN, cellDate);
				} else if (ColumnTypeInfo.DATETIME == colType) {
					values[i] = DateUtils.format(SQLUtils.DEFAULT_DATETIME_PATTERN, cellDate);
				} else {
					values[i] = cells[i].getContents();
				}

				// 布尔->VARCHAR
			} else if (type == CellType.BOOLEAN) {

				values[i] = cells[i].getContents();
				// 空->NULL
			} else if (type == CellType.EMPTY) {

				values[i] = null;
				// 缺省->VARCHAR
			} else {
				values[i] = cells[i].getContents();
			}
		}
		return values;
	}
	
//	/**
//	 * 取得一行单元格所包含的字段值。
//	 * 
//	 * @param line 一行单元格
//	 * @param column 数据库字段
//	 * @param generatorInfo 主键生成策略
//	 * @param idSize ID个数
//	 * @return 字段值
//	 */
//	private String[] getValues(Cell[] cells, Column[] column, IdGeneratorInfo generatorInfo,
//			int idSize) {
//		
//		String[] values = new String[column.length];
//		int len = (column.length < cells.length) ? column.length : cells.length;
//		for (int i = 0; i < len; ++i) {
//			CellType cellType = cells[i].getType();
//			if (cellType == CellType.EMPTY) {
//				values[i] = null;
//				continue ;
//			}
//			ColumnTypeInfo colType = column[i].getType();
//			switch(colType) {
//				case NUMBER:
//					NumberCell fcell = (NumberCell) cells[i];
//					values[i] = Double.toString(fcell.getValue());
//					break;
//					
//				case DATE:
//					DateCell dateCell = (DateCell) cells[i];
//					Date date = dateCell.getDate();
//					date.setTime(date.getTime() - 8 * 60 * 60 * 1000);
//					values[i] = DateUtils.format(SQLUtils.DEFAULT_DATE_PATTERN, date);
//					break;
//					
//				case DATETIME:
//					DateCell datetimeCell = (DateCell) cells[i];
//					Date datetime = datetimeCell.getDate();
//					datetime.setTime(datetime.getTime() - 8 * 60 * 60 * 1000);
//					values[i] = DateUtils.format(SQLUtils.DEFAULT_DATETIME_PATTERN, datetime);
//					break;
//					
//				case STRING:
//				case BOOLEAN:
//				case VIRTUAL:
//					values[i] = cells[i].getContents();
//					break;
//					
//				default:
//					break;
//			}
//		}
//		
//		return values;
//	}
}
